package com.artisan.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.artisan.model.Score;

public class ScoreDao extends BaseDao {
	public boolean addScore(Score score) {
		String sql = "insert into s_score values(null,?,?,?)";
		try {
			java.sql.PreparedStatement preparedStatement = con.prepareStatement(sql);
			preparedStatement.setInt(1, score.getStudent_id());
			preparedStatement.setInt(2, score.getCourse_id());
			preparedStatement.setInt(3, score.getScore());
			if (preparedStatement.executeUpdate() > 0)
				return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public List<Score> getScoreList(Score score) {
		List<Score> retList = new ArrayList<Score>();
		StringBuffer sqlString = new StringBuffer("select * from s_score");
		if (score.getStudent_id() != 0) {
			sqlString.append(" and student_id = " + score.getStudent_id());
		}
		if (score.getCourse_id() != 0) {
			sqlString.append(" and course_id =" + score.getCourse_id());
		}
		try {
			PreparedStatement preparedStatement = con
					.prepareStatement(sqlString.toString().replaceFirst("and", "where"));
			ResultSet executeQuery = preparedStatement.executeQuery();
			while (executeQuery.next()) {
				Score s = new Score();
				s.setId(executeQuery.getInt("id"));
				s.setStudent_id(executeQuery.getInt("student_id"));
				s.setCourse_id(executeQuery.getInt("course_id"));
				s.setScore(executeQuery.getInt("score"));
				retList.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return retList;
	}

	public boolean isAdd(Score score) {
		String sql = "select * from s_score where student_id=? and course_id = ?";
		try {
			PreparedStatement prst = con.prepareStatement(sql);// 把sql语句传给数据库操作对象
			prst.setInt(1, score.getStudent_id());
			prst.setInt(2, score.getCourse_id());
			ResultSet executeQuery = prst.executeQuery();
			if (executeQuery.next()) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean update(int id, int score) {
		String sql = "update s_score set score = ? where id=?";
		try {
			PreparedStatement preparedStatement = con.prepareStatement(sql);
			preparedStatement.setInt(1, score);
			preparedStatement.setInt(2, id);
			if (preparedStatement.executeUpdate() > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean delete(int id) {
		String sql = "delete from s_score where id=?";
		try {
			PreparedStatement preparedStatement = con.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
}
